﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Windows.Forms;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Nmo;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Trace;
using Entity;


namespace DataControls
{
    public class NX_DETAI_Data
    {
        DataBaseAccess dataBaseAccess = new DataBaseAccess();

        public void hienthi_DeTaitheoGV(DataGridView dtgNXDeTai, String magiaovien)
        {
            String excuteString = "SELECT * FROM NX_DETAI WHERE MAGVNX='" + magiaovien + "'";
            DataTable dataTable = new DataTable();
            dataTable = dataBaseAccess.getDataTable(excuteString);
            dtgNXDeTai.DataSource = dataTable;
        }


        public void timkiemtheo_TenDeTai(DataGridView dtgNXDeTai, String tendetai)
        {
            String excuteString = "SELECT  N.*, D.HOCKI, D.NAMHOC FROM DETAI D, NX_DETAI N " +
                                    "WHERE  D.MADT = N.MADT AND " +
                                    "TENDT LIKE'%" + tendetai + "'";
            DataTable dataTable = new DataTable();
            dataTable = dataBaseAccess.getDataTable(excuteString);
            dtgNXDeTai.DataSource = dataTable;
        }

        public void timkiemtheo_TenGV(DataGridView dtgNXDeTai, String tengiaovien)
        {
            String excuteString = "SELECT N.*,D.HOCKI, D.NAMHOC FROM NX_DETAI N, GIAOVIEN G, DETAI D " +
                                    "WHERE D.MADT = N.MADT AND G.MAGV= N.MAGVNX AND " +
                                    "G.TENGV LIKE'%" + tengiaovien + "'";
            DataTable dataTable = new DataTable();
            dataTable = dataBaseAccess.getDataTable(excuteString);
            dtgNXDeTai.DataSource = dataTable;
        }




        public void layten_DeTai(String madetai, ComboBox cb)
        {
            int a = 0;
            String excuteString = "SELECT TENDT FROM DETAI WHERE MADT='" + madetai + "'";
            String tendetai = dataBaseAccess.excuteReader(excuteString);
            for (int i = 0; i < cb.Items.Count; i++)
            {
                cb.SelectedIndex = i;
                if (tendetai==cb.Text)
                    a = i;
            }
            cb.SelectedIndex = a;
        }


        public void layten_GiaoVien(String magiaovien, ComboBox cb)
        {
            int a = 0;
            String excuteString = "SELECT TENGV FROM GIAOVIEN WHERE MAGV='" + magiaovien + "'";
            String tengiaovien = dataBaseAccess.excuteReader(excuteString);
            for (int i = 0; i < cb.Items.Count; i++)
            {
                cb.SelectedIndex = i;
                if (tengiaovien==cb.Text)
                    a = i;
            }
            cb.SelectedIndex = a;
        }

        public Boolean kiemtra_GiaoVienNX(String tengiaovien, String madetai)
        {
            String excuteString = "SELECT G.TENGV FROM GIAOVIEN G, DETAI D WHERE  D.MADT='" + madetai + "'AND D.MAGVHD = G.MAGV ";
            String giaovien = dataBaseAccess.excuteReader(excuteString);
            if (tengiaovien.Equals(giaovien))
            {
                return true;
            }
            else
                return false;
        }

        public int tongtiet_Nhanxet(String magiaovien, int hocki, int namhoc)
        {
            int tongtiet = 0;
            int sotietnhanxet = 0;
            String excuteString1 = "SELECT COUNT(*) FROM NX_DETAI N, DETAI D WHERE MAGVNX='" + magiaovien +
                                                                            "'AND D.HOCKI=" + hocki +
                                                                            "AND D.NAMHOC=" + namhoc +
                                                                            "AND D.MADT = N.MADT";
            sotietnhanxet =Convert.ToInt16(dataBaseAccess.excuteScalar(excuteString1));
            if (sotietnhanxet <= 0)
            {
                tongtiet = 0;
            }
            else
            {
                String excuteString = "SELECT SUM(N.SOTIETCHUAN) FROM NX_DETAI N, DETAI D WHERE N.MAGVNX='" + magiaovien +
                                                                                "'AND D.HOCKI=" + hocki +
                                                                                "AND D.NAMHOC=" + namhoc +
                                                                                "AND D.MADT = N.MADT";
                tongtiet = Convert.ToInt16(dataBaseAccess.excuteScalar(excuteString));
            }
            return tongtiet;
        }

    }
}
